IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMachineEnvVar' AND type = 'P')
     DROP PROCEDURE dbo.SaveMachineEnvVar
GO

CREATE PROCEDURE dbo.SaveMachineEnvVar
     @MachineId                int,
     @PackageExecutionId       bigint,
     @VarName                  varchar(100),
     @VarUser                  varchar(100),
     @VarVal                   varchar(7680),
     @PackageExecutionIdStr    varchar(30)   = NULL,
     @VarDeletedFlag           tinyint       = 0
AS
/*
 *
 * SaveMachineEnvVar
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 9/29/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @EnvIdForSrv int,
             @StartDt     datetime,
             @RC          int,
             @NOW         datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Handle case of PackageExecutionId
     IF ( @PackageExecutionId IS NULL AND @PackageExecutionIdStr IS NOT NULL )
     BEGIN -- Id value ALWAYS takes precedence
          SELECT @PackageExecutionId = CONVERT( bigint, @PackageExecutionIdStr )
     END
     ELSE 
     BEGIN -- All other cases we set the string to match the id
          SELECT @PackageExecutionIdStr = CONVERT( varchar(30), @PackageExecutionId )
     END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.MachineEnvVar
                     WHERE  MachineId  = @MachineId 
                     AND    VarName    = @VarName
                     AND    VarUser    = @VarUser )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          -- Get the env Id 
          SELECT @EnvIdForSrv = ISNULL(MAX(MachineEnvVarId),0) + 1
          FROM   dbo.MachineEnvVar
          WHERE  MachineId  = @MachineId 

          INSERT dbo.MachineEnvVar
               ( MachineId,
                 MachineEnvVarId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 PackageExecutionId,
                 VarDeletedFlag,
                 VarName,
                 VarUser,
                 VarVal )
          SELECT @MachineId,
                 @EnvIdForSrv,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @PackageExecutionId,
                 0, -- VarDeletedFlag
                 @VarName,
                 @VarUser,
                 @VarVal
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the env Id 
          SELECT @EnvIdForSrv = MachineEnvVarId 
          FROM   dbo.MachineEnvVar
          WHERE  MachineId = @MachineId 
          AND    VarName   = @VarName
          AND    VarUser   = @VarUser

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.MachineEnvVar
          WHERE  MachineId          = @MachineId 
          AND    MachineEnvVarId    = @EnvIdForSrv
          AND    CurrentRecordFlag  = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.MachineEnvVar 
          SET    EndDt              = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag  = 0
          WHERE  MachineId         = @MachineId 
          AND    MachineEnvVarId   = @EnvIdForSrv
          AND    CurrentRecordFlag = 1

          IF ( @VarDeletedFlag = 0 )
          BEGIN -- Do regular insert
               INSERT dbo.MachineEnvVar
                    ( MachineId,
                      MachineEnvVarId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      VarDeletedFlag,
                      VarName,
                      VarUser,
                      VarVal )
               SELECT @MachineId,
                      @EnvIdForSrv,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      0, -- VarDeletedFlag
                      @VarName,
                      @VarUser,
                      @VarVal
               FROM   dbo.MachineEnvVar
               WHERE  MachineId          = @MachineId 
               AND    MachineEnvVarId    = @EnvIdForSrv
               --AND    PackageExecutionId = @PEI
               AND    StartDt            = @StartDt
               -- Decide if there will be an insert
               AND  ( VarDeletedFlag = 1
                      --1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR ( @VarName                 != VarName )
                   OR ( @VarUser                 != VarUser )
                   OR (   ( @VarVal != VarVal ) 
                       OR ( @VarVal IS NULL     AND VarVal IS NOT NULL )
                       OR ( @VarVal IS NOT NULL AND VarVal IS NULL )
                      )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               -- See if it is already deleted, if so, break!
               IF EXISTS ( SELECT 1 
                           FROM   dbo.MachineEnvVar
                           WHERE  MachineId       = @MachineId
                           AND    MachineEnvVarId = @EnvIdForSrv
                           AND    StartDt         = @StartDt
                           AND    VarDeletedFlag  = 0 )
               BEGIN -- Not already deleted.
                    INSERT dbo.MachineEnvVar
                         ( MachineId,
                           MachineEnvVarId,
                           StartDt,
                           EndDt,
                           CurrentRecordFlag,
                           PackageExecutionId,
                           VarDeletedFlag,
                           VarName,
                           VarUser,
                           VarVal )
                    SELECT MachineId,
                           MachineEnvVarId,
                           @NOW, -- StartDt
                           '12/31/9999', -- EndDt
                           1, -- CurrentRecordFlag
                           @PackageExecutionId,
                           1, -- VarDeletedFlag
                           VarName,
                           VarUser,
                           VarVal
                    FROM   dbo.MachineEnvVar
                    WHERE  MachineId          = @MachineId
                    AND    MachineEnvVarId    = @EnvIdForSrv
                    --AND    PackageExecutionId = @PEI
                    AND    StartDt            = @StartDt

                    SELECT @RC = @@ROWCOUNT 
               END
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO
